param(
	[Parameter(Mandatory=$False)]
	[ValidateSet("vdi_user", "user_role_license")]
	[string]$by_db_query_type="vdi_user",
	[Parameter(Mandatory=$False)]
	[ValidateSet("fid", "soeid","all")]
	[string]$by_db_user_type="fid"
)
Function Start-BulkThreads(){	# -Action { Stop-Process -Id $PID }
	# useing parallel threads to run scripts in the same time so that it will speedup the running duration
	#reference https://www.codeproject.com/tips/895840/multi-threaded-powershell-cookbook
    param(
    	[Parameter(Mandatory=$True)]
    	[Object[]]$vdilist,             # the vdi list that scripts to be ran
    	[Parameter(Mandatory=$True)]    
    	[ScriptBlock]$Action,           # the actual action that each thread will run
    	[Parameter(Mandatory=$True)]
		[AllowNull()]		
    	[Object[]]$ActionParams,        # the parameters that each thread needs, this can be a single or array of parameter
    	[Parameter(Mandatory=$false)]   
    	[int]$maxCnt=100                # the default amount of powershell threads, if this number is big, then the system might get stuck
    )
	$results=@() # this is used to collect the result from each thread
	$vdiCounts = $vdilist.Count
	If( $vdiCounts -eq 1 ){ # no need to create thread pool to run it
		If( [string]::IsNullOrWhiteSpace($vdilist[0]) ){
		    write-error "vdilist only contains 1 line but it's null"
		}else{& $Action $vdilist[0] 1 $ActionParams}
	}else{
		$vdiCounts = $vdilist.Count ; $throttleLimit=$maxCnt ; If($vdiCounts -le $maxCnt ){$throttleLimit=$vdiCounts}
		# creaate the thread pool and run each thread based the vdi list amount
		$SessionState = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
		$Pool = [runspacefactory]::CreateRunspacePool(1, $throttleLimit, $SessionState, $Host)
		$Pool.Open() ; $threads = @() ; $cnt=1
		$handles = foreach($line in $vdilist){
			if( [string]::IsNullOrWhiteSpace($line ) ){continue}
			$powershell = [powershell]::Create().AddScript($Action).AddArgument($line).AddArgument($cnt).AddArgument($ActionParams)
			$powershell.RunspacePool = $Pool ;	$powershell.BeginInvoke() ; $threads += $powershell ; $cnt++
		}
		# wait each thread to be done and then collect its return value
		do{
		  $i = 0 ;  $done = $true
		  foreach ($handle in $handles) {
			if($handle -ne $null){if($handle.IsCompleted){$results+=$threads[$i].EndInvoke($handle);$threads[$i].Dispose();$handles[$i]=$null}else{$done = $false}}
			$i++
		  }
		  if (-not $done) { Start-Sleep -Milliseconds 200 }
		} until ($done)
	}
	return $results # return the value collection from each thread and then export to csv or take other further actions
}
$db_list=@(
	'NAM,PROD',
	'NAM,UAT',
	'NAM,DEV',
	'APAC,PROD',
	'APAC,UAT',
	'EUR,PROD',
	'EUR,UAT'
)
$query_db_action={
	param($line,$cnt,$ActionParams)
	$domain,$env=$line.split(',').ToUpper()
	$db_config_file,$by_db_query_type=$ActionParams
	write-host "Trying to get data from [$cnt-$domain-$env] database`n"
	Function Connect-Sql(){
		param(
			[Parameter(Mandatory=$True)]
			[string]$DBServer,
			[string]$DBPwd,
			[Parameter(Mandatory=$False)]
			[string]$DBS= "ICGDB"
		)
		$connection = New-Object System.Data.SqlClient.SqlConnection
		$connection.ConnectionString = "Server=$DBServer;uid=aa_dbread;pwd=$DBPwd;Database=$DBS;Integrated Security=False;"
		Try{
		   $connection.Open() ; return $connection
		}Catch{
		   Write-Error "Failed to connect to DB server $DBServer with user 'aa_dbread'!"; return $null
		}Finally{}
	}
	Function Execute-Sql(){
		param(
			[Parameter(Mandatory=$True)]
			$connection,
			[Parameter(Mandatory=$False)]
			[string]$query
		)
		Try{
			$adapter = New-Object System.Data.sqlclient.sqlDataAdapter (new-object system.data.sqlclient.sqlcommand($query,$connection))
			$dataset = New-Object System.Data.DataSet ; $adapter.Fill($dataSet) | Out-Null
			return $dataSet.Tables[0]
		}Catch{
			Write-Error "Failed to run sql syntax $query in $domain $env DB $DBServer"
			return $null
		}Finally{}
	}
	$db_config=[XML](get-content $db_config_file)
	$DBServer = $db_config.SelectSingleNode("//$domain/$env/server").InnerText
	$DBPwd = $db_config.SelectSingleNode("//$domain/$env/pwd").InnerText
	$DBPwd=[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((ConvertTo-SecureString $DBPwd)))
	$connection=Connect-Sql -DBServer $DBServer -DBPwd $DBPwd
	$vdi_filter=Switch ("$domain/$env"){
	    "NAM/PROD"{'SW';break}
	    "NAM/UAT"{'MW';break}
	    "NAM/DEV"{'MWDINT';break}
	    "APAC/PROD"{'APACSGAW';break}
	    "APAC/UAT"{'APACHKAW';break}
	    "EUR/PROD"{'LD';break}
	    "EUR/UAT"{'FF';break}
	}
	$db_query=Switch ($by_db_query_type){
	    "vdi_user"{ # SELECT du.ADNetBIOS as Domain,dc.HostName,du.UserName
			"select '$domain' as DBDomain,'$env' as DBEnv,du.ADNetBIOS as Region,dc.HostName,du.UserName
			FROM [ICGDB].[dbo].[Clients] dc,[ICGDB].[dbo].[Users] du
			where dc.UserId = du.Id 
			and (du.UserName like 'aa%' or du.UserName like 'a0%') 
			and dc.HostName like '$vdi_filter%'"
			break}
	    "user_role_license"{ 
			"select '$domain' as DBDomain,'$env' as DBEnv,dc.HostName, du.ADNetBIOS as Region,du.UserName, dr.Name as Roles, df.Feature as licenses
			from [ICGDB].[dbo].[Clients] dc,[ICGDB].[dbo].[Users] du,[ICGDB].[dbo].[Roles] dr,[ICGDB].[dbo].[UserRoles] dur,[ICGDB].[dbo].[Features] df
			where du.IsDeleted=0
            and dc.UserId=du.id
			and du.id=dur.UserId 
			and dr.id=dur.RoleId 
			and df.UserId=du.id"
		}
	}	
	$db_detail=Execute-Sql -connection $connection -query $db_query
	$connection.close()
	write-host -ForegroundColor Yellow "Done getting data from [$cnt-$domain-$env] database`n"
	return $db_detail
}
$this_path = Split-Path -Parent ($MyInvocation.MyCommand.Definition)
$db_config_file="$this_path\db_config.xml"
If( test-path $db_config_file ){
	$csv_file,$sort_filter=Switch($by_db_query_type){
	    "vdi_user"{"db_vdi_user.csv",@("HostName","UserName");break}
	    "user_role_license"{"db_user_role_license.csv","*"}
	}
	Start-BulkThreads $db_list $query_db_action $db_config_file,$by_db_query_type|sort $sort_filter -Unique|sort DBDomain|Export-Csv -Path $this_path\$csv_file -UseCulture -NoTypeInformation -Encoding UTF8 -Force -ErrorAction Stop
    Switch($by_db_user_type){
        "fid"{(cat $csv_file) -inotmatch "`"[a-zA-Z]{2}\d{5}`""|out-file $csv_file -Encoding UTF8 -Force }
        "soeid"{(cat $csv_file) -imatch "`"[a-zA-Z]{2}\d{5}`""|out-file $csv_file -Encoding UTF8 -Force }
    }
	write-host -ForegroundColor Yellow "The result csv has been saved in '$this_path\$csv_file'"
}else{
    write-error "$db_config_file cannot be found!(it contains the db password)"
}


